﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TpData.Interfaces;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Diagnostics;

namespace TpData.Layer {
    public class BaseSqlDataLayer : IDataLayer {

        #region Members

        private IntPtr handle;
        private Component component = new Component();
        private bool disposed = false;
        private string connectionString;

        #endregion

        #region Contructor

        public BaseSqlDataLayer() { Dispose(false); }

        #endregion

        #region Initialize

        public void Initialize(string connectionString) {
            this.connectionString = connectionString;
        }

        #endregion

        #region Properties

        protected string ConnectionString {
            get { return this.connectionString; }
        }

        #endregion

        #region Methods

        protected void CloseConnection(SqlConnection conn) {
            try {
                conn.Close();
            } catch { }
        }

        public void Dispose() {
            Dispose(true);
            // This object will be cleaned up by the Dispose method.
            // Therefore, you should call GC.SupressFinalize to
            // take this object off the finalization queue
            // and prevent finalization code for this object
            // from executing a second time.
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing) {
            // Check to see if Dispose has already been called.
            if (!this.disposed) {
                // If disposing equals true, dispose all managed
                // and unmanaged resources.
                if (disposing) {
                    // Dispose managed resources.
                    component.Dispose();
                }

                // Call the appropriate methods to clean up
                // unmanaged resources here.
                // If disposing is false,
                // only the following code is executed.
                CloseHandle(handle);
                handle = IntPtr.Zero;

                // Note disposing has been done.
                disposed = true;

            }
        }

        #endregion

        #region Functions

        protected SqlConnection OpenConnection() {
            SqlConnection conn = new SqlConnection(this.ConnectionString);
            conn.Open();
            return conn;
        }

        #region DataReader

        protected virtual SqlDataReader GetDataReader(string sql) {
            SqlCommand cmd = new SqlCommand(sql, this.OpenConnection());
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        protected virtual SqlDataReader GetDataReader(string sql, params SqlParameter[] parameters) {
            SqlCommand cmd = new SqlCommand(sql, this.OpenConnection());
            if (parameters != null) {
                foreach (SqlParameter parameter in parameters) {
                    if (parameter != null) {
                        cmd.Parameters.Add(parameter);
                    }
                }
            }
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        protected virtual SqlDataReader GetDataReaderFromStoredProcedure(string name, params SqlParameter[] parameters) {
            SqlCommand cmd = new SqlCommand(name, this.OpenConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null) {
                foreach (SqlParameter parameter in parameters) {
                    if (parameter != null) {
                        cmd.Parameters.Add(parameter);
                    }
                }
            }
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion

        #region DataTable

        protected virtual DataTable GetDataTable(string sql) {
            return GetDataTable(sql, null, new SqlParameter[] { });
        }

        protected virtual DataTable GetDataTable(string sql, params SqlParameter[] parameters) {
            return GetDataTable(sql, null, parameters);
        }

        protected virtual DataTable GetDataTable(string sql, DataTable table, params SqlParameter[] parameters) {
            using (SqlConnection connection = this.OpenConnection()) {
                SqlCommand cmd = new SqlCommand(sql, connection);
                if (parameters != null) {
                    foreach (SqlParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                if (table == null) {
                    table = new DataTable();
                }
                adapter.Fill(table);
                return table;
            }
        }

        protected virtual DataTable GetDataTableFromStoredProcedure(string name, params SqlParameter[] parameters) {
            using (SqlConnection connection = this.OpenConnection()) {
                SqlCommand cmd = new SqlCommand(name, connection);
                cmd.CommandType = CommandType.StoredProcedure;

                if (parameters != null) {
                    foreach (SqlParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                adapter.Fill(table);
                return (table);
            }
        }

        #endregion

        #region Executes

        protected virtual int ExecuteNonQuery(string sql) {
            return ExecuteNonQuery(sql, null);
        }

        protected virtual int ExecuteNonQuery(string sql, params SqlParameter[] parameters) {
            using (SqlConnection connection = this.OpenConnection()) {
                SqlCommand cmd = new SqlCommand(sql, connection);
                if (parameters != null) {
                    foreach (SqlParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                return cmd.ExecuteNonQuery();
            }
        }

        protected virtual int ExecuteNonQuery(string sql, out int identity, params SqlParameter[] parameters) {
            using (SqlConnection connection = this.OpenConnection()) {
                SqlCommand cmd = new SqlCommand(sql, connection);
                if (parameters != null) {
                    foreach (SqlParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                int result = cmd.ExecuteNonQuery();
                identity = GetIdentity(connection);
                return result;
            }
        }

        protected virtual int ExecuteStoredProcedure(string name, params SqlParameter[] parameters) {
            int ret = 0;
            using (SqlConnection connection = this.OpenConnection()) {
                SqlCommand cmd = new SqlCommand(name, connection);
                if (parameters != null) {
                    foreach (SqlParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                cmd.CommandType = CommandType.StoredProcedure;
                try {
                    ret = (int)cmd.ExecuteNonQuery();
                    //ret = (int)cmd.ExecuteScalar();
                } catch (Exception ex) {
                    ret = 1;
                    Debug.Print(ex.Message);
                } 
                return ret;
            }
        }

        protected virtual int ExecuteStoredProcedure(SqlConnection connection, string name, params SqlParameter[] parameters) {
            SqlCommand cmd = new SqlCommand(name, connection);
            if (parameters != null) {
                foreach (SqlParameter parameter in parameters) {
                    if (parameter != null) {
                        cmd.Parameters.Add(parameter);
                    }
                }
            }
            cmd.CommandType = CommandType.StoredProcedure;
            return cmd.ExecuteNonQuery();
        }

        protected virtual int ExecuteStoredProcedure(string name, out int identity, params SqlParameter[] parameters) {
            using (SqlConnection connection = this.OpenConnection()) {
                SqlCommand cmd = new SqlCommand(name, connection);
                if (parameters != null) {
                    foreach (SqlParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                cmd.CommandType = CommandType.StoredProcedure;
                int result = cmd.ExecuteNonQuery();
                identity = this.GetIdentity(connection);
                return result;
            }
        }

        #endregion

        #region Scalar

        protected virtual object GetScalar(string sql) {
            using (SqlConnection connection = this.OpenConnection()) {
                SqlCommand cmd = new SqlCommand(sql, connection);
                return cmd.ExecuteScalar();
            }
        }

        protected virtual object GetScalar(SqlConnection connection, string sql) {
            SqlCommand cmd = new SqlCommand(sql, connection);
            return cmd.ExecuteScalar();
        }

        protected virtual object GetScalar(string sql, params SqlParameter[] parameters) {
            using (SqlConnection connection = this.OpenConnection()) {
                SqlCommand cmd = new SqlCommand(sql, connection);
                if (parameters != null) {
                    foreach (SqlParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                return cmd.ExecuteScalar();
            }
        }

        protected virtual object GetScalar(SqlConnection connection, string sql, params SqlParameter[] parameters) {
            SqlCommand cmd = new SqlCommand(sql, connection);
            if (parameters != null) {
                foreach (SqlParameter parameter in parameters) {
                    if (parameter != null) {
                        cmd.Parameters.Add(parameter);
                    }
                }
            }
            return cmd.ExecuteScalar();
        }

        protected virtual object GetScalarFromStoredProcedure(string name, params SqlParameter[] parameters) {
            using (SqlConnection connection = this.OpenConnection()) {
                SqlCommand cmd = new SqlCommand(name, connection);
                if (parameters != null) {
                    foreach (SqlParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                cmd.CommandType = CommandType.StoredProcedure;
                return cmd.ExecuteScalar();
            }
        }

        #endregion

        #region Basics

        protected virtual int GetIdentity(SqlConnection connection) {
            SqlCommand cmd = new SqlCommand("SELECT @@Identity;", connection);
            return Convert.ToInt32(cmd.ExecuteScalar());
        }

        protected object CheckNull(object value) {
            if (value == null) {
                return DBNull.Value;
            }
            return value;
        }

        protected object CheckNull(DateTime dt) {
            if ((dt == DateTime.MinValue) || (dt == DateTime.MaxValue)) return DBNull.Value;
            return dt;
        }

        protected object CheckNull(Guid guid) {
            if (guid == Guid.Empty) return DBNull.Value;
            return guid;
        }

        protected string GetString(IDataReader reader, string field) {
            return (reader[field] as string);
        }

        protected byte GetByte(IDataReader reader, string field) {
            if (reader[field] != DBNull.Value) return (byte)reader[field];
            return 0;
        }

        protected long GetLong(IDataReader reader, string field) {
            if (reader[field] != DBNull.Value) return (Int32)reader[field];
            return 0;
        }

        protected Guid GetGuid(IDataReader reader, string field) {
            if (reader[field] != DBNull.Value) return (Guid)reader[field];
            return Guid.Empty;
        }

        protected DateTime GetDateTime(IDataReader reader, string field) {
            return this.GetDateTime(reader, field, DateTime.MinValue);
        }

        protected DateTime GetDateTime(IDataReader reader, string field, DateTime defaultValue) {
            if (reader[field] != DBNull.Value) return (DateTime)reader[field];
            return defaultValue;
        }

        protected int GetInt32(IDataReader reader, string field) {
            return this.GetInt32(reader, field, 0);
        }

        protected int GetInt32(IDataReader reader, string field, int defaultValue) {
            if (reader[field] != DBNull.Value) return (int)reader[field];
            return defaultValue;
        }

        protected long GetInt64(IDataReader reader, string field) {
            return this.GetInt64(reader, field, 0);
        }

        protected long GetInt64(IDataReader reader, string field, int defaultValue) {
            if (reader[field] != DBNull.Value) return (long)reader[field];
            return defaultValue;
        }

        protected decimal GetDecimal(IDataReader reader, string field) {
            return this.GetDecimal(reader, field, 0);
        }

        protected decimal GetDecimal(IDataReader reader, string field, long defaultValue) {
            if (reader[field] != DBNull.Value) return (decimal)reader[field];
            return defaultValue;
        }

        protected bool GetBoolean(IDataReader reader, string field) {
            return this.GetBoolean(reader, field, false);
        }

        protected bool GetBoolean(IDataReader reader, string field, bool defaultValue) {
            if (reader[field] != DBNull.Value) return (bool)reader[field];
            return defaultValue;
        }

        protected DataRelation CreateDataRelation(string relationName, DataSet ds, string parentColumn, string childColumn) {
            string[] parent = parentColumn.Split('.');
            if (parent.Length != 2) {
                throw new ArgumentException("parentColumn must contain valid column name: \"table.colum\"", "parentColumn");
            }

            string[] child = childColumn.Split('.');
            if (child.Length != 2) {
                throw new ArgumentException("childColumn must contain valid column name: \"table.colum\"", "childColumn");
            }

            return new DataRelation(relationName, ds.Tables[parent[0]].Columns[parent[1]], ds.Tables[child[0]].Columns[child[1]]);
        }

        protected int SaveStream(SqlConnection connection, string column, byte[] pointer, Stream stream) {
            int bufferSize = 1024;
            SqlCommand cmd = new SqlCommand(string.Format("UPDATETEXT {0} @Pointer @Offset 0 @Bytes", column), connection);

            SqlParameter paramPointer = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
            paramPointer.Value = pointer;

            SqlParameter paramData = cmd.Parameters.Add("@Bytes", SqlDbType.Image, bufferSize);

            SqlParameter paramOffset = cmd.Parameters.Add("@Offset", SqlDbType.Int);
            paramOffset.Value = 0;

            BinaryReader br = new BinaryReader(stream);
            byte[] buffer = br.ReadBytes(bufferSize);
            int offset = 0;

            while (buffer.Length > 0) {
                paramData.Value = buffer;
                cmd.ExecuteNonQuery();
                offset += buffer.Length;
                paramOffset.Value = offset;
                buffer = br.ReadBytes(bufferSize);
            }
            return offset;
        }

        protected void MergeAndUpdate(DataTable newData, string sql, params SqlParameter[] parameters) {
            using (SqlConnection connection = this.OpenConnection()) {
                SqlCommand cmd = new SqlCommand(sql, connection);
                if (parameters != null) {
                    foreach (SqlParameter parameter in parameters) {
                        if (parameter != null) {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                }
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds, newData.TableName);

                SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
                ds.Merge(newData, false, MissingSchemaAction.Ignore);
                adapter.Update(ds.Tables[0]);
            }
        }

        #endregion

        #endregion

        #region DLL Calls

        [System.Runtime.InteropServices.DllImport("Kernel32")]
        private extern static Boolean CloseHandle(IntPtr handle);

        #endregion
    }
}
